#region << 版 本 注 释 >>
/*----------------------------------------------------------------
 * 版权所有 (c) 2025   保留所有权利。
 * CLR版本：4.0.30319.42000
 * 机器名称：DESKTOP-GLJ2Q8J
 * 公司名称：
 * 命名空间：Npoi.ExcelOperateTests
 * 唯一标识：108af68a-4575-448a-ad56-ee3b70fd850f
 * 文件名：StudentExcelOperator
 * 当前用户域：DESKTOP-GLJ2Q8J
 * 
 * 创建者： Guo_79991
 * 电子邮箱：799919859@qq.com
 * 创建时间：2025/10/9 14:00:06
 * 版本：V1.0.0
 * 描述：
 *
 * ----------------------------------------------------------------
 * 修改人：
 * 时间：
 * 修改说明：
 *
 * 版本：V1.0.1
 *----------------------------------------------------------------*/
#endregion << 版 本 注 释 >>

using Npoi.ExcelOperate;
using Npoi.ExcelOperate.Standard;
using NPOI.SS.UserModel;
using System;
using System.Collections.Generic;
using System.IO;
using System.Windows.Forms;

namespace Npoi.ExcelOperateTests
{
    /// <summary>
    /// 学生Excel操作类 - 实现增删改查完整功能
    /// </summary>
    public class StudentExcelOperator : ExcelOperator, IGetModel<Ticket>, IGetList<Student>, IWriteExcel<Student>, IDeleteExcel, IUpdateExcel
    {
        public StudentExcelOperator(string filePath, string savePath = null)
        {
            this.FilePath = filePath;
            this.SavePath = savePath ?? filePath.Replace(".xlsx", "_modified.xlsx");
        }

        public StudentExcelOperator()
        {
            string basePath = AppDomain.CurrentDomain.BaseDirectory;
            this.FilePath = Path.Combine(basePath, @"ExcelTest\2025-03-12_哈尔滨_南京.xlsx");
            this.SavePath = Path.Combine(basePath, @"ExcelTest\2025-03-12_哈尔滨_南京_modified.xlsx");
        }

        #region 查询操作

        /// <summary>
        /// 获取车票模型
        /// </summary>
        public Ticket GetModel()
        {
            try
            {
                return GetOne((ISheet sheet, Ticket model) =>
                {
                    // 读取基本信息
                    if (sheet.LastRowNum >= 0)
                    {
                        IRow headerRow = sheet.GetRow(0);
                        if (headerRow != null)
                        {
                            model.Departure = GetCellValue(headerRow, 0) ?? "未知";
                            model.Destination = GetCellValue(headerRow, 1) ?? "未知";
                        }
                    }

                    // 读取车次列表
                    model.TrainNums = new List<string>();
                    for (int i = 1; i <= sheet.LastRowNum; i++)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            string trainNum = GetCellValue(row, 0);
                            if (!string.IsNullOrEmpty(trainNum))
                            {
                                model.TrainNums.Add(trainNum);
                            }
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"获取车票模型失败: {ex.Message}");
                return null;
            }
        }

        /// <summary>
        /// 获取学生列表
        /// </summary>
        public List<Student> GetList()
        {
            try
            {
                return GetAll((ISheet sheet, List<Student> list) =>
                {
                    for (int i = 1; i <= sheet.LastRowNum; i++) // 从第1行开始，跳过标题行
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null && row.GetCell(0) != null)
                        {
                            var student = new Student
                            {
                                Name = GetCellValue(row, 0),
                                Age = int.TryParse(GetCellValue(row, 1), out int age) ? age : 0,
                                Class = GetCellValue(row, 2),
                                Score = decimal.TryParse(GetCellValue(row, 3), out decimal score) ? score : 0
                            };
                            list.Add(student);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"获取学生列表失败: {ex.Message}");
                return new List<Student>();
            }
        }

        /// <summary>
        /// 根据姓名查询学生
        /// </summary>
        public Student GetStudentByName(string name)
        {
            var students = GetList();
            return students?.Find(s => s.Name == name);
        }

        #endregion

        #region 写入操作

        /// <summary>
        /// 写入单个学生数据
        /// </summary>
        public bool WriteData(Student data)
        {
            try
            {
                return WriteListData(new List<Student> { data });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"写入学生数据失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 写入学生列表数据
        /// </summary>
        public bool WriteListData(List<Student> dataList)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(0) ?? workbook.CreateSheet("学生信息");

                    // 创建标题行
                    IRow headerRow = sheet.GetRowIfNull(0);
                    headerRow.GetCellIfNull(0).SetCellValue("姓名");
                    headerRow.GetCellIfNull(1).SetCellValue("年龄");
                    headerRow.GetCellIfNull(2).SetCellValue("班级");
                    headerRow.GetCellIfNull(3).SetCellValue("分数");

                    // 写入数据
                    int startRow = Math.Max(1, sheet.LastRowNum + 1); // 从现有数据的下一行开始
                    for (int i = 0; i < dataList.Count; i++)
                    {
                        IRow row = sheet.GetRowIfNull(startRow + i);
                        var student = dataList[i];

                        row.GetCellIfNull(0).SetCellValue(student.Name);
                        row.GetCellIfNull(1).SetCellValue(student.Age);
                        row.GetCellIfNull(2).SetCellValue(student.Class);
                        row.GetCellIfNull(3).SetCellValue((double)student.Score);
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"写入学生列表失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 添加新车次
        /// </summary>
        public bool AddTrainNum(string trainNum)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(0);
                    int newRowIndex = sheet.LastRowNum + 1;
                    IRow newRow = sheet.CreateRow(newRowIndex);
                    newRow.CreateCell(0).SetCellValue(trainNum);
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"添加车次失败: {ex.Message}");
                return false;
            }
        }

        #endregion

        #region 删除操作

        /// <summary>
        /// 删除指定行
        /// </summary>
        public bool DeleteRow(int sheetIndex, int rowIndex)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);

                    if (rowIndex <= sheet.LastRowNum && sheet.GetRow(rowIndex) != null)
                    {
                        sheet.RemoveRow(sheet.GetRow(rowIndex));

                        if (rowIndex < sheet.LastRowNum)
                        {
                            sheet.ShiftRows(rowIndex + 1, sheet.LastRowNum, -1);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"删除行失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 根据姓名删除学生
        /// </summary>
        public bool DeleteStudentByName(string name)
        {
            try
            {
                var students = GetList();
                int rowIndex = students.FindIndex(s => s.Name == name);
                if (rowIndex >= 0)
                {
                    // +1 因为数据从第1行开始（跳过标题行）
                    return DeleteRow(0, rowIndex + 1);
                }
                return false;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"删除学生失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 删除指定Sheet页
        /// </summary>
        public bool DeleteSheet(int sheetIndex)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    if (sheetIndex >= 0 && sheetIndex < workbook.NumberOfSheets)
                    {
                        workbook.RemoveSheetAt(sheetIndex);
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"删除Sheet页失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 清空所有数据（保留标题行）
        /// </summary>
        public bool ClearAllData()
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(0);

                    // 从最后一行开始删除到第一行（保留标题行）
                    for (int i = sheet.LastRowNum; i >= 1; i--)
                    {
                        IRow row = sheet.GetRow(i);
                        if (row != null)
                        {
                            sheet.RemoveRow(row);
                        }
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"清空数据失败: {ex.Message}");
                return false;
            }
        }

        #endregion

        #region 修改操作

        /// <summary>
        /// 修改指定单元格的值
        /// </summary>
        public bool UpdateCellValue(int sheetIndex, int rowIndex, int cellIndex, object newValue)
        {
            try
            {
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(sheetIndex);
                    IRow row = sheet.GetRowIfNull(rowIndex);
                    ICell cell = row.GetCellIfNull(cellIndex);

                    SetCellValue(cell, newValue);
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"修改单元格失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 修改学生信息
        /// </summary>
        public bool UpdateStudent(string oldName, Student newStudent)
        {
            try
            {
                var students = GetList();
                int rowIndex = students.FindIndex(s => s.Name == oldName);
                if (rowIndex >= 0)
                {
                    // +1 因为数据从第1行开始（跳过标题行）
                    int actualRowIndex = rowIndex + 1;

                    bool success = UpdateCellValue(0, actualRowIndex, 0, newStudent.Name);
                    success &= UpdateCellValue(0, actualRowIndex, 1, newStudent.Age);
                    success &= UpdateCellValue(0, actualRowIndex, 2, newStudent.Class);
                    success &= UpdateCellValue(0, actualRowIndex, 3, newStudent.Score);

                    return success;
                }
                return false;
            }
            catch (Exception ex)
            {
                MessageBox.Show($"修改学生信息失败: {ex.Message}");
                return false;
            }
        }

        /// <summary>
        /// 批量更新车次信息
        /// </summary>
        public bool BatchUpdateTrainNums(List<string> newTrainNums)
        {
            try
            {
                // 先清空现有车次数据
                if (!ClearAllData()) return false;

                // 添加新车次数据
                return SaveExcelToCopy(workbook =>
                {
                    ISheet sheet = workbook.GetSheetAt(0);
                    for (int i = 0; i < newTrainNums.Count; i++)
                    {
                        IRow row = sheet.GetRowIfNull(i + 1); // +1 跳过标题行
                        row.GetCellIfNull(0).SetCellValue(newTrainNums[i]);
                    }
                });
            }
            catch (Exception ex)
            {
                MessageBox.Show($"批量更新车次失败: {ex.Message}");
                return false;
            }
        }

        #endregion

        #region 辅助方法

        /// <summary>
        /// 设置单元格值
        /// </summary>
        private void SetCellValue(ICell cell, object value)
        {
            if (value == null)
            {
                cell.SetCellValue("");
                return;
            }

            switch (value)
            {
                case string str:
                    cell.SetCellValue(str);
                    break;
                case int intVal:
                    cell.SetCellValue(intVal);
                    break;
                case double doubleVal:
                    cell.SetCellValue(doubleVal);
                    break;
                case decimal decimalVal:
                    cell.SetCellValue(Convert.ToDouble(decimalVal));
                    break;
                case float floatVal:
                    cell.SetCellValue(floatVal);
                    break;
                case bool boolVal:
                    cell.SetCellValue(boolVal);
                    break;
                case DateTime dateVal:
                    cell.SetCellValue(dateVal);
                    break;
                default:
                    cell.SetCellValue(value.ToString());
                    break;
            }
        }

        /// <summary>
        /// 获取Excel文件信息
        /// </summary>
        public string GetFileInfo()
        {
            try
            {
                var workbook = ReadExcel();
                if (workbook == null) return "文件读取失败";

                ISheet sheet = workbook.GetSheetAt(0);
                return $"Sheet名称: {sheet.SheetName}, 总行数: {sheet.LastRowNum + 1}, 总列数: {sheet.GetRow(0)?.LastCellNum ?? 0}";
            }
            catch (Exception ex)
            {
                return $"获取文件信息失败: {ex.Message}";
            }
        }

        #endregion
    }
}